-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Insert]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Update]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Delete]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Load]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectAll]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_SelectBy_SoToKhai_And_MaHaiQuan_And_MaLoaiHinh_And_NamDangKy]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectBy_SoToKhai_And_MaHaiQuan_And_MaLoaiHinh_And_NamDangKy]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_HangHoa_DeleteBy_SoToKhai_And_MaHaiQuan_And_MaLoaiHinh_And_NamDangKy]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_DeleteBy_SoToKhai_And_MaHaiQuan_And_MaLoaiHinh_And_NamDangKy]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Insert]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@STT int,
	@SoHieuContainer nvarchar(128),
	@SoSealContainer nvarchar(128),
	@SoSealHaiQuan nvarchar(128),
	@GhiChu nvarchar(512)
AS
INSERT INTO [dbo].[t_HAIQUAN_QLHS_HangHoa]
(
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[STT],
	[SoHieuContainer],
	[SoSealContainer],
	[SoSealHaiQuan],
	[GhiChu]
)
VALUES
(
	@SoToKhai,
	@MaHaiQuan,
	@MaLoaiHinh,
	@NamDangKy,
	@STT,
	@SoHieuContainer,
	@SoSealContainer,
	@SoSealHaiQuan,
	@GhiChu
)

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Update]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@STT int,
	@SoHieuContainer nvarchar(128),
	@SoSealContainer nvarchar(128),
	@SoSealHaiQuan nvarchar(128),
	@GhiChu nvarchar(512)
AS

UPDATE
	[dbo].[t_HAIQUAN_QLHS_HangHoa]
SET
	[SoHieuContainer] = @SoHieuContainer,
	[SoSealContainer] = @SoSealContainer,
	[SoSealHaiQuan] = @SoSealHaiQuan,
	[GhiChu] = @GhiChu
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
	AND [STT] = @STT

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_InsertUpdate]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@STT int,
	@SoHieuContainer nvarchar(128),
	@SoSealContainer nvarchar(128),
	@SoSealHaiQuan nvarchar(128),
	@GhiChu nvarchar(512)
AS
IF EXISTS(SELECT [SoToKhai], [MaHaiQuan], [MaLoaiHinh], [NamDangKy], [STT] FROM [dbo].[t_HAIQUAN_QLHS_HangHoa] WHERE [SoToKhai] = @SoToKhai AND [MaHaiQuan] = @MaHaiQuan AND [MaLoaiHinh] = @MaLoaiHinh AND [NamDangKy] = @NamDangKy AND [STT] = @STT)
	BEGIN
		UPDATE
			[dbo].[t_HAIQUAN_QLHS_HangHoa] 
		SET
			[SoHieuContainer] = @SoHieuContainer,
			[SoSealContainer] = @SoSealContainer,
			[SoSealHaiQuan] = @SoSealHaiQuan,
			[GhiChu] = @GhiChu
		WHERE
			[SoToKhai] = @SoToKhai
			AND [MaHaiQuan] = @MaHaiQuan
			AND [MaLoaiHinh] = @MaLoaiHinh
			AND [NamDangKy] = @NamDangKy
			AND [STT] = @STT
	END
ELSE
	BEGIN
	INSERT INTO [dbo].[t_HAIQUAN_QLHS_HangHoa]
	(
			[SoToKhai],
			[MaHaiQuan],
			[MaLoaiHinh],
			[NamDangKy],
			[STT],
			[SoHieuContainer],
			[SoSealContainer],
			[SoSealHaiQuan],
			[GhiChu]
	)
	VALUES
	(
			@SoToKhai,
			@MaHaiQuan,
			@MaLoaiHinh,
			@NamDangKy,
			@STT,
			@SoHieuContainer,
			@SoSealContainer,
			@SoSealHaiQuan,
			@GhiChu
	)	
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Delete]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@STT int
AS

DELETE FROM 
	[dbo].[t_HAIQUAN_QLHS_HangHoa]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
	AND [STT] = @STT

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_DeleteDynamic]
	@WhereCondition nvarchar(500)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = 'DELETE FROM [dbo].[t_HAIQUAN_QLHS_HangHoa] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_Load]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@STT int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[STT],
	[SoHieuContainer],
	[SoSealContainer],
	[SoSealHaiQuan],
	[GhiChu]
FROM
	[dbo].[t_HAIQUAN_QLHS_HangHoa]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
	AND [STT] = @STT
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectBy_SoToKhai_And_MaHaiQuan_And_MaLoaiHinh_And_NamDangKy]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectBy_SoToKhai_And_MaHaiQuan_And_MaLoaiHinh_And_NamDangKy]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[STT],
	[SoHieuContainer],
	[SoSealContainer],
	[SoSealHaiQuan],
	[GhiChu]
FROM
	[dbo].[t_HAIQUAN_QLHS_HangHoa]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectDynamic]
	@WhereCondition nvarchar(500),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = 
'SELECT 
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[STT],
	[SoHieuContainer],
	[SoSealContainer],
	[SoSealHaiQuan],
	[GhiChu]
FROM [dbo].[t_HAIQUAN_QLHS_HangHoa] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_HangHoa_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[STT],
	[SoHieuContainer],
	[SoSealContainer],
	[SoSealHaiQuan],
	[GhiChu]
FROM
	[dbo].[t_HAIQUAN_QLHS_HangHoa]	

GO
